package sql;

import java.sql.*;

public class Dao {

    public void query() throws SQLException {
        Connection conn = getConn();
        String sql = "select * from table";
        PreparedStatement pstm = conn.prepareStatement(sql);
        ResultSet resultSet = pstm.executeQuery();

    }

    public void update() throws SQLException {
        Connection conn = getConn();
        String sql = " update table set a=?";

        PreparedStatement pstm = conn.prepareStatement(sql);
        pstm.setObject(1, "32");
        pstm.executeUpdate();
    }

    /**
     * 添加锁, 多并发的形式
     *
     * @throws SQLException
     */
    public void updateWithLock() throws SQLException {
        //添加表锁的sql

        Connection conn = getConn();
        String lockSql = "start transaction ;" +
                "lock tables t1 write ,t2 read " +
                "update table set a=?;" + "" +
                "commit; unlock tables";
        //添加行级锁的sql =>共享锁
        String sql = "start transaction ;" +
                "select  * from table where ... lock in share mode";
        //添加行级锁的sql =>排他锁
        String sql2 = "start transaction ;" +
                "select  * from table where ... for update";
        PreparedStatement pstm = conn.prepareStatement(sql);
        pstm.setObject(1, "32");
        pstm.executeUpdate();
    }

    public void delete() throws SQLException {
        Connection conn = getConn();
        String sql = " delete * from table ";

        PreparedStatement pstm = conn.prepareStatement(sql);
        pstm.executeUpdate();
    }

    public void insert() throws SQLException {
        Connection conn = getConn();
        String sql = " insert into table values ...";

        PreparedStatement pstm = conn.prepareStatement(sql);
        pstm.setObject(1, "32");
        pstm.executeUpdate();
    }

    private Connection getConn() {

        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "";
            String user = "root";
            String psw = "admin";
            connection = DriverManager.getConnection(url, user, psw);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

